package com.fancy.core.dal;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.support.TransactionTemplate;

import com.fancy.core.model.UserModel;

@Repository
public class UserDaoImpl implements UserDao {

	private static final String INSERT = "insert into users(id,name,age,sex,createDate) values(hello_seq.nextval,?,?,?,sysdate)";
	private static final String DEL = "delete from users where id=?";
	private static final String UPDATE = "update users set name=?,age=?,sex=? where id=?";
	private static final String SELECT = "select * from users where id in(?)";
	private static final String SELECT_LIST = "select * from  (select a.*, rownum r from (select * from users order by id desc) a where rownum <= ?) where r > ?";
	private static final String SELECT_COUNT = "select count(*) from users";
	

	private static final Log log = LogFactory.getLog(UserDaoImpl.class);
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public int insert(final UserModel user) {
		log.info("insert");
		return jdbcTemplate.update(INSERT, user.getName(),user.getAge(),user.getSex());
	}
	
	@Override
	public int del(Integer id) {
		return jdbcTemplate.update(DEL, id);
	}

	@Override
	public int update(UserModel user) {
		return jdbcTemplate.update(UPDATE, user.getName(),user.getAge(),user.getSex(),user.getId());
	}

	@Override
	public UserModel select(Integer id) {
		return jdbcTemplate.queryForObject(SELECT, new UserRowMapper(), id);
	}

	@Override
	public List<UserModel> selectList(Integer start, Integer count) {
		return jdbcTemplate.query(SELECT_LIST, new Object[] { start + count,  start }, new UserRowMapper());
	}

	@Override
	public int selectListCount() {
		return jdbcTemplate.queryForObject(SELECT_COUNT, Integer.class);
	}
	
	public class UserRowMapper implements RowMapper<UserModel> {

		public UserModel mapRow(ResultSet rs, int index) throws SQLException {
			UserModel user = new UserModel(rs.getInt("id"),
					rs.getString("name"), rs.getInt("age"),
					rs.getInt("sex"),rs.getDate("createDate"));
			return user;
		}

	}

}